﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using Liju.Model;

namespace LiJu.Db
{
    public class ProductDbHelper
    {

        /// <summary>
        /// 添加产品
        /// </summary>
        /// <returns></returns>
        public static bool AddProduct(ProductModel productModel)
        {
            string strMySql = $"INSERT INTO product (ProductName, Barcode, ImgNum,TaskNum,Status,CreateTime,Creater)VALUES('{productModel.ProductName}', '{productModel.BarCode}', {productModel.ImgNum}, {productModel.TaskNum}, {productModel.Status}, '{productModel.CreateTime}', '{productModel.Creater}'); ";
            int result = DalMySQL.ExecuteNonQuery(strMySql);
            return result>0;
        }


        /// <summary>
        /// 修改产品
        /// </summary>
        /// <returns></returns>
        public static bool UpdateProduct(ProductModel productModel)
        {
            string strMySql = $"update product set ProductName='{productModel.ProductName}', BarCode='{productModel.BarCode}', ImgNum={productModel.ImgNum}, TaskNum={productModel.TaskNum}, Status= { productModel.Status}, UpdateUser ='{productModel.UpdateUser}', UpdateTime='{productModel.UpdateTime}' where Id={productModel.Id}";
            int result = DalMySQL.ExecuteNonQuery(strMySql);
            return result > 0;
        }

        /// <summary>
        /// 获取最大Id
        /// </summary>
        /// <returns></returns>
        public static int GetMaxNewAddProductId()
        {
           string strMySql = "SELECT AUTO_INCREMENT FROM information_schema.tables WHERE table_name = 'product';";
           var  result = DalMySQL.GetSingle(strMySql);
            if (result == null) return 1;
           int.TryParse(result.ToString(), out int maxId);
           return maxId;
        }


        /// <summary>
        /// 获取最大Id
        /// </summary>
        /// <returns></returns>
        public static int GetMaxProductId()
        {
            string strMySql = "SELECT max(Id) FROM product;";
            var result = DalMySQL.GetSingle(strMySql);
            if (result == null) return 1;
            int.TryParse(result.ToString(), out int maxId);
            return maxId;
        }

        /// <summary>
        /// 查询所有产品
        /// </summary>
        /// <returns></returns>
        public static DataTable GetAllProduct()
        {
            string strMySql = "select * from product";
            DataTable dt = DalMySQL.ExecuteDataTable(strMySql);
            return dt;
        }
        /// <summary>
        /// 查询所有产品
        /// </summary>
        /// <returns></returns>
        public static List<ProductModel> GetAllProducts()
        {
            //获取没有删除的产品
            string strMySql = "select * from product where status!=2";
            DataTable dt = DalMySQL.ExecuteDataTable(strMySql);
            var products = DbExtension.DataTableToList<ProductModel>(dt);

            return products;
        }
        /// <summary>
        /// 查询所有产品
        /// </summary>
        /// <returns></returns>
        public static ProductModel GetProductById(long productId)
        {
            //获取没有删除的产品
            string strMySql = $"select * from product where id={productId} and status!=2";
            DataTable dt = DalMySQL.ExecuteDataTable(strMySql);
            var products = DbExtension.DataTableToList<ProductModel>(dt);

            return products.FirstOrDefault();
        }

        /// <summary>
        /// 根据条码查询产品
        /// </summary>
        /// <returns></returns>
        public static ProductModel GetProductByCode(string strCode)
        {
            string strMySql = $"select * from product where BarCode='{strCode}' and status=1 ";
            DataTable dt = DalMySQL.ExecuteDataTable(strMySql);
            var products = DbExtension.DataTableToList<ProductModel>(dt);

            return products.FirstOrDefault();
        }

        /// <summary>
        /// 根据条码查询产品
        /// </summary>
        /// <returns></returns>
        public static ProductModel GetProductBySN(string strSN)
        {
            string strMySql = $"select * from product where status=1 ";
            DataTable dt = DalMySQL.ExecuteDataTable(strMySql);
            for (int i = dt.Rows.Count - 1; i > -1; i--)
            {
                //从后往前找 指定开头的产品信息;20241103zhanghl增加指定结尾的产品信息;
                if (strSN.Trim().StartsWith(dt.Rows[i]["BarCode"].ToString().Trim()) || strSN.Trim().EndsWith(dt.Rows[i]["BarCode"].ToString().Trim()))
                {
                    break;
                }
                else//移除非本型号产品
                {
                    dt.Rows.RemoveAt(i);
                }
            }
            var products = DbExtension.DataTableToList<ProductModel>(dt);
            //返回最后一条记录
            if (products.Count > 0)
            {
                return products[products.Count - 1];
            }
            else
            {
                return null;
            }
        }


        /// <summary>
        /// 更改产品状态 Status
        /// 0:禁用 1:启用 2:删除
        /// </summary>
        public static bool UpdateProductStatus(long id,int status)
        {
            string strMySql = $"update product  set Status={status} where id={id};";
            int result = DalMySQL.ExecuteNonQuery(strMySql);
            return result>0;
        }

        /// <summary>
        /// 删除产品
        /// 0:禁用 1:启用 2:删除
        /// </summary>
        public static bool DeleteProduct(long id)
        {
            string strMySql = $"update product  set Status=2 where id={id};";
            int result = DalMySQL.ExecuteNonQuery(strMySql);
            return result>0;
        }
        /// <summary>
        /// 分页查询 add zhl
        /// </summary>
        /// <param name="pageSize"></param>
        /// <param name="pageNum"></param>
        /// <returns></returns>
        public static List<ProductModel> GetPageDataProducts(int pageSize, int pageNum)
        {
            int offset = (pageNum - 1) * pageSize;
            //获取没有删除的产品
            string strMySql = "select * from product where status!=2 order by Id limit @pageSize offset @offset";
            MySql.Data.MySqlClient.MySqlParameter[] parameters = new MySql.Data.MySqlClient.MySqlParameter[2];
            parameters[0] = new MySql.Data.MySqlClient.MySqlParameter("@pageSize", MySql.Data.MySqlClient.MySqlDbType.Int32);
            parameters[1] = new MySql.Data.MySqlClient.MySqlParameter("@offset", MySql.Data.MySqlClient.MySqlDbType.Int32);
            parameters[0].Value = pageSize;
            parameters[1].Value = offset;
            DataTable dt = DalMySQL.ExecuteDataTable(strMySql, parameters);
            var products = DbExtension.DataTableToList<ProductModel>(dt);

            return products;
        }
        /// <summary>
        /// 获取总行数 add zhl
        /// </summary>
        /// <returns></returns>
        public static int GetTotalRowCount()
        {
            string strMySql = "select count(1) from product where status!=2";
            var result = DalMySQL.GetSingle(strMySql);
            if (result == null) return 0;
            int.TryParse(result.ToString(), out int count);
            return count;
        }
    }
}
